import pymysql
from faker import Faker

def generate_and_insert_data(num_records, db_config):
    fake = Faker()
    connection = pymysql.connect(**db_config)
    cursor = connection.cursor()

    # 创建表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS test_data (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100),
            email VARCHAR(100),
            address TEXT,
            phone_number VARCHAR(50)
        )
    """)

    # 插入数据
    insert_stmt = """
        INSERT INTO test_data (name, email, address, phone_number)
        VALUES (%s, %s, %s, %s)
    """
    
    batch_size = 500000  # 批量插入大小
    batch_data = []

    for i in range(num_records):
        data = (
            fake.name(),
            fake.email(),
            fake.address().replace('\n', ' '),  # 替换换行符
            fake.phone_number()
        )
        batch_data.append(data)

        if len(batch_data) >= batch_size:
            cursor.executemany(insert_stmt, batch_data)
            connection.commit()
            print(f'{i + 1} records inserted.')
            batch_data = []  # 清空批量数据

    # 插入剩余数据
    if batch_data:
        cursor.executemany(insert_stmt, batch_data)
        connection.commit()

    print(f'Total {num_records} records inserted.')

    cursor.close()
    connection.close()

if __name__ == "__main__":
    db_config = {
        'user': 'root',
        'password': '123456',
        'host': '192.168.88.50',
        'database': 'testdb'
    }
    generate_and_insert_data(10000001, db_config)
